﻿use master
GO

--DROP DATABASE GENERALMODEL
CREATE DATABASE GENERALMODEL
GO

use GENERALMODEL
GO

-- Danh sách MENU
-- Nếu parentsID là NULL : MENU Chính
-- Ngược lại, panel thuộc menu parentsID
-- sortNumber: số thứ tự dùng để sắp sếp
-- position: vị trí đặt menu
CREATE TABLE Panels
(
	panelID			INT IDENTITY NOT NULL,
	[name]			NVARCHAR(100) NOT NULL,
	description		NVARCHAR(500),
	sortNumber		INT NOT NULL,
	parentsID		INT,
	positionID		INT,
	isActived		BIT DEFAULT(1) NOT NULL,
	CONSTRAINT pk_leftPanelID_LeftPanels Primary KEY(panelID)
)
GO

-- Thường có 2 vị trí: Left, Top
CREATE TABLE Positions
(
	positionID		INT IDENTITY NOT NULL,
	positionName	NVARCHAR(100) NOT NULL,
	description		NVARCHAR(500),
	isActived		BIT DEFAULT(1) NOT NULL,
	CONSTRAINT pk_positionID_Positions PRIMARY KEY(positionID)
)
GO

-- Thông tin hỗ trợ trực tuyến
-- img: tên hình cần hiển thị
---- với yahoo: từ 1 -> 15
---- với skype: 

-- YM
CREATE TABLE SupportOnlineYM
(
	soID			INT IDENTITY NOT NULL,
	nickname		VARCHAR(100) NOT NULL,
	defaultString	NVARCHAR(100),
	CONSTRAINT pk_soID_SupportOnlineYM PRIMARY KEY(soID)
)
GO

-- Skype
CREATE TABLE SupportOnlineSkype
(
	soID			INT IDENTITY NOT NULL,
	nickname		VARCHAR(100) NOT NULL,
	defaultString	NVARCHAR(100),
	CONSTRAINT pk_soID_SupportOnlineSkype PRIMARY KEY(soID)
)
GO

-- thông tin quảng cáo
-- picture: đường dẫn hình ảnh quảng cáo
-- link: đường dẫn web
CREATE TABLE Banners
(
	bannerID		INT IDENTITY NOT NULL,
	bannerName		NVARCHAR(200) NOT NULL,
	picture			VARCHAR(200) NOT NULL,
	link			VARCHAR(200) NOT NULL,
	isActived		BIT DEFAULT(1) NOT NULL,
	CONSTRAINT pk_pannerID_Panners PRIMARY KEY(bannerID)
)
GO

-- thông tin đối tác
-- company: tên đối tác
-- avatar: đường dẫn hình ảnh đại diện đối tác
CREATE TABLE Partners
(
	partnerID		INT IDENTITY NOT NULL,
	company			NVARCHAR(200) NOT NULL,
	avatar			VARCHAR(200) NOT NULL,
	link			VARCHAR(200) NOT NULL,
	isActived		BIT DEFAULT(1) NOT NULL,
	CONSTRAINT pk_partnerID_Partners PRIMARY KEY(partnerID)
)
GO

-- Thông tin website
--DROP TABLE ContactInformation
CREATE TABLE ContactInformation
(
	id				INT IDENTITY NOT NULL,
	shortName		VARCHAR(50),
	companyName		NVARCHAR(200),
	slogan			NVARCHAR(200),
	logo			VARCHAR(200),
	address			NVARCHAR(200),
	phone			VARCHAR(100),
	email			VARCHAR(100),
	website			VARCHAR(100),
	copyright		VARCHAR(100),
	designed		VARCHAR(100),
	powered			VARCHAR(100)
)
GO

CREATE TABLE Contacts
(
	contactID		INT IDENTITY NOT NULL,
	contactName		NVARCHAR(100) NOT NULL,
	address			NVARCHAR(200),
	phone			VARCHAR(20),
	email			VARCHAR(100),
	subject			NVARCHAR(200),
	[content]		NVARCHAR(500),
	status			BIT DEFAULT(0),
	CONSTRAINT pk_contactID_Contacts PRIMARY KEY(contactID)
)
GO

CREATE TABLE Products
(
	productID		INT IDENTITY NOT NULL,
	productName		NVARCHAR(200) NOT NULL,
	price			MONEY,
	country			NVARCHAR(50) NOT NULL,
	productContents	NVARCHAR(MAX),
	countView		INT DEFAULT(0) NOT NULL,
	productDate		DATETIME DEFAULT(GETDATE()),
	productTypeID	INT NOT NULL,
	manufacturerID	INT NOT NULL,
	panelID			INT NOT NULL,
	isActived		BIT DEFAULT(1) NOT NULL,
	CONSTRAINT pk_productID_Products PRIMARY KEY(productID)
)
GO

CREATE TABLE ProductTypes
(
	typeID			INT IDENTITY NOT NULL,
	typeName		NVARCHAR(100) NOT NULL,
	description		NVARCHAR(500) NOT NULL,
	isActived		BIT DEFAULT(1) NOT NULL,
	CONSTRAINT pk_typeID_ProductTypes PRIMARY KEY(typeID)
)
GO

CREATE TABLE Manufacturers
(
	manufacturerID	INT IDENTITY NOT NULL,
	[name]			NVARCHAR(200) NOT NULL,
	description		NVARCHAR(500),
	isActived		BIT DEFAULT(1) NOT NULL,
	CONSTRAINT pk_manufacturerID_Manufacturers PRIMARY KEY(manufacturerID)
)
GO

CREATE TABLE News
(
	newsID			INT IDENTITY NOT NULL,
	newsTitle		NVARCHAR(200) NOT NULL,
	newsSubContent	NVARCHAR(500) NOT NULL,
	newsContent		NVARCHAR(MAX) NOT NULL,
	newsDate		DATETIME DEFAULT(GETDATE()) NOT NULL,
	newsPicture		VARCHAR(100),
	newsPictureName	NVARCHAR(200),
	countView		INT DEFAULT(0) NOT NULL,
	typeID			INT NOT NULL,
	panelID			INT NOT NULL,
	isActived		BIT DEFAULT(1) NOT NULL,
	CONSTRAINT pk_newsID_News PRIMARY KEY(newsID)
)
GO

--DROP TABLE NewsTypes
CREATE TABLE NewsTypes
(
	typeID			INT IDENTITY NOT NULL,
	typeName		NVARCHAR(200) NOT NULL,
	description		NVARCHAR(500),
	isActived		BIT DEFAULT(1) NOT NULL,
	CONSTRAINT pk_typeID_NewsTypes PRIMARY KEY(typeID)
)
GO

CREATE TABLE Accounts
(
	userName		VARCHAR(100) NOT NULL,
	password		VARCHAR(100) NOT NULL,
	fullName		NVARCHAR(200) NOT NULL,
	address			NVARCHAR(200),
	phone			VARCHAR(15),
	email			VARCHAR(100),
	birthday		DATETIME,
	avatar			VARCHAR(100),
	hqID			INT,
	answer			NVARCHAR(200),
	createDate		DATETIME DEFAULT(GETDATE()),
	isActived		BIT DEFAULT(1) NOT NULL,
	CONSTRAINT pk_userName_Accounts PRIMARY KEY(userName)
)
GO

CREATE TABLE HiddenQuestions
( 
	hqID			INT IDENTITY NOT NULL,
	question		NVARCHAR(200) NOT NULL,
	isActived		BIT DEFAULT(1) NOT NULL,
	CONSTRAINT pk_hqID_HiddenQuestions PRIMARY KEY(hqID)
)
GO

CREATE TABLE Administrators
(
	adminID			VARCHAR(100) NOT NULL,
	password		VARCHAR(100) NOT NULL,
	roleID			INT NOT NULL,
	CONSTRAINT pk_adminID_Administrators PRIMARY KEY(adminID)
)
GO

CREATE TABLE Const
(
	newsNumberInOnePage		INT NOT NULL,
	ymImgNumber				INT NOT NULL,
	skypeImgSource			VARCHAR(50) NOT NULL
)
GO

--DROP TABLE HotLines
CREATE TABLE HotLines
(
	hotLineID		INT IDENTITY NOT NULL,
	hotLineName		NVARCHAR(50) NOT NULL,
	number			VARCHAR(20) NOT NULL,
	CONSTRAINT pk_hotLineID_HotLines PRIMARY KEY(hotLineID)
)
GO